﻿<cfsilent>
	<cfscript>
	
		datetimeAdvice = getProperty("serviceFactory").getBean("datetimeAdvice");
		propAdvice = getProperty("serviceFactory").getBean("studentPropertyAdvice");
		
		termDateStart = year( now() );
		
		/* 当前在校生判断 grade + schooling_length > ? */
		if ( month( now() ) < 9 ) {
			termDateStart = termDateStart - 1;
		}
		
		sql = "SELECT sch_name FROM t_school ";
								
		queryObj = new Query( datasource=application.dnsSlave );
		
		rs_school = queryObj.execute( sql=sql ).getResult();
		
		temFile = GetTempDirectory() & createUUID() & ".xls";
		downFile = URLEncodedFormat( rs_school.sch_name & "海外学院本科生学分指导-学生名单", "utf-8");
		
		sql = " SELECT 
					a.stu_id, 
					a.stu_name, a.stu_name_english, a.stu_sex, a.nationality, 
					a.stu_idcard, 
					b.tch_name, 
					c.student_prop,
					 (select count(*) from t_student s where s.tch_id = b.tch_id) countStu 
				FROM 
					t_student a 
					INNER JOIN t_teacher b ON b.tch_id = a.tch_id  
					INNER JOIN t_student_status c ON c.stu_id = a.stu_id 
				ORDER BY 
					countStu desc,b.tch_name, a.stu_name";
				
		queryObj = new Query( datasource=application.dnsSlave ) ;
		queryObj.addParam( name="yearNow", value=termDateStart, cfsqltype="cf_sql_decimal", scale=1  );
		
		rs_student = queryObj.execute( sql=sql ).getResult();
		

		excel = getProperty("serviceFactory").getBean("spreadSheetObject");

		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew("学生名单", false);

		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "学院,专业,教师,学号,姓名,护照名,护照号,国籍,性别,在校");
		
		row=1;
		for ( i=1; i LTE rs_student.recordCount; i++){
			row++;
			
			/* 构建学生信息 */
			
			/* 构建考生数据 */
			rowData = "海外学院" & "," & "汉语言"
						& "," & rs_student["tch_name"][i]
						& "," & rs_student["stu_id"][i]
						& "," & replace(rs_student["stu_name"][i],",","，","all")
						& "," & replace(rs_student["stu_name_english"][i],",","，","all")
						& "," & replace(rs_student["stu_idcard"][i],",","，","all")
						& "," & rs_student["nationality"][i];
			
			if ( rs_student["stu_sex"][i] eq "1" ) {
				rowData = rowData & ",男";
			}
			else {
				rowData = rowData & ",女";
			}
			
			/* 学生属性 */
			propAdvice.parseProperty( rs_student["student_prop"][i] );
			
			if ( propAdvice.getInSchool() ) {
				rowData = rowData & ",是";
			}
			else {
				rowData = rowData & ",否";
			}
			
			/* 写入学生信息 */
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
			
		
			
		}
	
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);	

	</cfscript>
    
    <cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />
    
</cfsilent>